package com.softeem.dao.impl;

import com.softeem.bean.Book;
import com.softeem.dao.BookDao;
import com.softeem.utils.BaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl extends BaseDao<Book> implements BookDao {

    @Override
    public int addBook(Book book)  throws SQLException{
        return this.update("insert into t_book values(null,?,?,?,?,?,?)",
                book.getName(),book.getPrice(),book.getAuthor(),book.getSales(),book.getStock(),book.getImgPath());
    }

    @Override
    public int deleteBookById(Integer id) throws SQLException {
        return this.update("delete from t_book where id = ?" , id);
    }

    @Override
    public int updateBook(Book book) throws SQLException {
        return this.update("update t_book set name=?,price=?,author=?,sales=?,stock=?,img_path=? where id = ?",
                book.getName(),book.getPrice(),book.getAuthor(),book.getSales(),book.getStock(),book.getImgPath(),book.getId());
    }

    @Override
    public Book queryBookById(Integer id) throws SQLException {
        return this.getBean("select * from t_book where id = ?" , id);
    }

    @Override
    public List<Book> queryBooks() throws SQLException {
        return this.getBeanList("select * from t_book order by id desc");
    }

    /**
     * 此方法用来获取表中的总记录数
     * @return
     */
    @Override
    public Integer queryForPageTotalCount() throws SQLException {
        return this.getValue("select count(*) from t_book").intValue();
    }

    /**
     * 分页查询方法
     * @param begin 起始下标
     * @param pageSize 显示数据条数
     * @return
     */
    @Override
    public List<Book> queryForPageItems(int begin, int pageSize)  throws SQLException{
        return this.getBeanList("select * from t_book order by id desc limit ?,?" , begin , pageSize);
    }

    @Override
    public Integer queryForPageTotalCount(String name, String author, Double min, Double max)  throws SQLException{
        StringBuilder sql = new StringBuilder("select count(*) from t_book where 1=1 ");
        ArrayList params = new ArrayList();//保存参数的
        if(name != null && !"".equals(name)){
            sql.append(" and name like concat('%',?,'%') ");
            params.add(name);//如果name不为空就把name保存到集合
        }
        if( author!=null && !"".equals(author) ){
            sql.append(" and author like ? ");
            params.add("%"+author+"%");//如果author不为空就把author保存到集合
        }
        //00000000000000000000000000000000000000000000000000000000
        if( (min != null && min > 0) && (max != null && max > 0)){
            sql.append(" and price BETWEEN ? AND ?");
            if( min < max ){
                params.add(min);
                params.add(max);
            }else{
                params.add(max);
                params.add(min);
            }
        }else if( min != null && min > 0 ){
            sql.append(" and price >= ?");
            params.add(min);
        }else if( max != null && max > 0 ){
            sql.append(" and price <= ?");
            params.add(max);
        }
        System.out.println(sql);
        //00000000000000000000000000000000000000000000000000000000
        return this.getValue(sql.toString(),params.toArray()).intValue();
    }

    @Override
    public List<Book> queryForPageItems(int begin, int pageSize, String name, String author, Double min, Double max)  throws SQLException{

        StringBuilder sql = new StringBuilder("select * from t_book where 1=1 ");
        ArrayList params = new ArrayList();//保存参数的
        if(name != null && !"".equals(name)){
            sql.append(" and name like concat('%',?,'%') ");
            params.add(name);//如果name不为空就把name保存到集合
        }
        if( author!=null && !"".equals(author) ){
            sql.append(" and author like ? ");
            params.add("%"+author+"%");//如果author不为空就把author保存到集合
        }
        //00000000000000000000000000000000000000000000000000000000
        if( (min != null && min > 0) && (max != null && max > 0)){
            sql.append(" and price BETWEEN ? AND ?");
            if( min < max ){
                params.add(min);
                params.add(max);
            }else{
                params.add(max);
                params.add(min);
            }
        }else if( min != null && min > 0 ){
            sql.append(" and price >= ?");
            params.add(min);
        }else if( max != null && max > 0 ){
            sql.append(" and price <= ?");
            params.add(max);
        }
        sql.append(" order by id desc limit ? , ?");
        params.add(begin);
        params.add(pageSize);
        System.out.println(sql);
        //00000000000000000000000000000000000000000000000000000000
        return this.getBeanList(sql.toString(),params.toArray());
    }

    @Override
    public Book queryBookByNameAndPrice(String name, Double price) throws SQLException {
        return super.getBean("select * from t_book where name=? and price = ?" ,name,price);

    }
}
